import { query, execute } from '../server/utils/db'

// 1. 删除数据库中前端已删除的项
// 2. 更新数据库中已存在的项
// 3. 插入数据库中新增的项
export default async function upsertRows(
  table: string,
  rows: any[],
  excludeKeys: string[] = ['id'],
  fk?: string,
  fkValue?: any
) {
  // 1. 删除数据库中前端已删除的项
  if (fk && fkValue !== undefined) {
    // 子表：按外键删除
    const dbRows = (await query<{ id: number }[]>(`SELECT id FROM ${table} WHERE ${fk} = ?`, [fkValue])).results
    const keepIds = rows.filter(r => r.id).map(r => r.id)
    const deleteIds = dbRows.map(r => r.id).filter(id => !keepIds.includes(id))
    if (deleteIds.length) {
      await execute(`DELETE FROM ${table} WHERE id IN (${deleteIds.map(() => '?').join(',')})`, deleteIds)
    }
  } else {
    // 主表：全量同步，删除未在 rows 里的
    const dbRows = (await query<{ id: number }[]>(`SELECT id FROM ${table}`)).results
    const keepIds = rows.filter(r => r.id).map(r => r.id)
    const deleteIds = dbRows.map(r => r.id).filter(id => !keepIds.includes(id))
    if (deleteIds.length) {
      await execute(`DELETE FROM ${table} WHERE id IN (${deleteIds.map(() => '?').join(',')})`, deleteIds)
    }
  }
  // 2. 新增/更新
  for (const row of rows) {
    const { id, ...fields } = row
    const keys = Object.keys(fields).filter(k => !excludeKeys.includes(k))
    const values = keys.map(k => fields[k])
    if (id) {
      // 更新
      if (keys.length) {
        const updates = keys.map(key => `${key} = ?`).join(', ')
        await execute(
          `UPDATE ${table} SET ${updates} WHERE id = ?`,
          [...values, id]
        )
      }
    } else {
      // 新增
      if (keys.length) {
        const columns = keys.join(', ')
        const placeholders = keys.map(() => '?').join(', ')
        await execute(
          `INSERT INTO ${table} (${columns}) VALUES (${placeholders})`,
          values
        )
      }
    }
  }
}